"""
Case Type   : 基础算子性能提升-快速参数化增加视图查询功能
Case Name   : 已删除的表的视图缓存，也占缓存数量
Create At   : 2025-10-17
Owner       : sungang14
Description :
    1、set enable_query_parameterization = on;set max_parameterized_query_stored = 2;
    2、创建表t1,t2，并分表对两张表执行insert语句1条
    3、删除t1表
    4、查看视图，
    5、执行1条t2表update语句
    6、查看视图，
Expect      :
    1、设置成功
    2、成功
    3、成功
    4、只有1条t2表的缓存记录
    5、成功
    6、视图只显示最后执行的1条update记录
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

logger = Logger()


class SlowSQLTestCase(unittest.TestCase):
    def setUp(self):
        logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.comsh = CommonSH('PrimaryDbUser')
        self.show_query_parameterization = 'show enable_query_parameterization;'
        self.set_query_parameterization = "enable_query_parameterization = ON"
        self.set_query_parameterization_01 = "enable_query_parameterization = OFF"
        self.show_query_stored = 'show max_parameterized_query_stored;'
        self.set_query_stored = "max_parameterized_query_stored = 2"
        self.set_query_stored_01 = "max_parameterized_query_stored = 256"
        self.table_name = f"query_parameterization_views_case0008"
        self.table_name1 = f"query_parameterization_views_case0008_1"
        self.table_name2 = f"query_parameterization_views_case0008_2"
        self.prepare_data_00_1 = f"drop table if exists {self.table_name1} cascade;"
        self.prepare_data_00_2 = f"drop table if exists {self.table_name2} cascade;"
        self.prepare_data_01_1 = f"create table {self.table_name1}(id int,v1 int,v2 int,v3 varchar(255));"
        self.prepare_data_01_2 = f"create table {self.table_name2}(id int,v1 int,v2 int,v3 varchar(255));"
        self.prepare_data_02 = f"INSERT INTO {self.table_name1} values(1,1,2,'abc');" \
                               f"INSERT INTO {self.table_name2} values(1,1,2,'abc');" \
                               f"drop table {self.table_name1} cascade;" \
                               f"select * from query_parameterization_views() where parameterized_query like '%{self.table_name}%';" \
                               f"UPDATE {self.table_name2} SET id = 7 WHERE id = 1;" \
                               f"select * from query_parameterization_views() where parameterized_query like '%{self.table_name}%';"
        text = '--step1:开启enable_query_parameterization参数;expect:开启成功--'
        logger.info(text)
        show_cmd = self.comsh.execut_db_sql(self.show_query_parameterization)
        logger.info(show_cmd)
        self.assertEquals('off', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_query_parameterization)
        logger.info(set_cmd1)
        show_cmd = self.comsh.execut_db_sql(self.show_query_stored)
        logger.info(show_cmd)
        self.assertEquals('256', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        set_cmd = self.comsh.execute_gsguc('reload',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           self.set_query_stored)
        logger.info(set_cmd)

    def test_slowsql(self):
        text = '--step2:创建表1和表2;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00_1)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01_1)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00_2)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_01_2)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, data_cmd, '执行失败' + text)

        text = '--step3:对两张表执行insert语句，删除表1，查看query_parameterization_views，执行1条t2表update语句，查看query_parameterization_views;expect:第一次查询只有一条表2的insert记录，第二次查询只有一条表2的update记录--'
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_02)
        logger.info(data_cmd)
        self.assertEqual(data_cmd.count(f'INSERT 0 1'), 2, '执行失败' + text)
        self.assertEqual(data_cmd.count(f'UPDATE 1'), 1, '执行失败' + text)
        self.assertEqual(data_cmd.count(f'1 row'), 2, '执行失败' + text)
        self.assertEqual(data_cmd.count(f'| UPDATE     |'), 1, '执行失败' + text)
        self.assertEqual(data_cmd.count(f'| INSERT     |'), 1, '执行失败' + text)

    def tearDown(self):
        text = '--step4:清理环境，恢复默认值;expect:成功--'
        logger.info(text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00_1)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        data_cmd = self.comsh.execut_db_sql(self.prepare_data_00_2)
        logger.info(data_cmd)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, data_cmd, '执行失败' + text)
        set_cmd = self.comsh.execute_gsguc('reload',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           self.set_query_stored_01)
        logger.info(set_cmd)
        show_cmd = self.comsh.execut_db_sql(self.show_query_stored)
        logger.info(show_cmd)
        self.assertEquals('256', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        set_cmd1 = self.comsh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            self.set_query_parameterization_01)
        logger.info(set_cmd1)
        show_cmd = self.comsh.execut_db_sql(self.show_query_parameterization)
        logger.info(show_cmd)
        self.assertEquals('off', show_cmd.splitlines()[-2].strip(),
                          '执行失败' + text)
        logger.info(f'-----{os.path.basename(__file__)} end-----')
